﻿#region File Header
/***********************************************************************
 * This code is released into the public domain under WTFPL. Simply put:
 * You may use this code in whatever way you want, commercial or
 * personal.
 * 
 * Check exceltocsv.codeplex.com for updates to the original code.
************************************************************************
Author: Donald Beals
Date: August 26th, 2013
Description: The main class for the program.
****************************** Change Log ******************************
08.26.13 - Created initial file. (dbeals)
08.27.13 - Added code to handle timeout issue. (dbeals)
09.03.13 - Overhaul; moved code to helper classes, split up processing
 * methods, added support for multiple worksheets. Also added support
 * for command line arguments and help.
***********************************************************************/
#endregion

using System;
using System.Collections.Generic;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace ExcelToCSV
{
	class Program
	{
		static void Main(string[] args)
		{
			Logger.Initialize(args);

			var flags = Helper.GetFlags(args);
			if(flags.ContainsKey("help") || flags.ContainsKey("h"))
			{
				var cursorTop = 0;
				Logger.LogInformation("ExcelToCSV conversion utility");
				Logger.LogInformation("exceltocsv.codeplex.com");
				Logger.LogInformation("Version 1.2");
				Logger.LogInformation("The purpose of this utility is to convert an XLSX file to a comma/quote");
				Logger.LogInformation("delimited file (meaning each cell in is surrounded in quotes, each column is");
				Logger.LogInformation("separated by a comma and each row is on a new line.)");
				Logger.LogInformation(new string('-', 29));
				Logger.LogInformation("exceltocsv.exe [/split-sheets[=true/false]] [/ext=\"ext\"] [/h[elp]]");
				Logger.LogInformation(string.Empty);

				cursorTop = Console.CursorTop;
				Logger.LogInformation("split-sheets");
				Console.CursorTop = cursorTop;
				Console.CursorLeft = 20;
				Logger.LogInformation("If true, the utility will write each sheet to a separate");
				Console.CursorLeft = 20;
				Logger.LogInformation("file, using the sheet's name as the filename and the");
				Console.CursorLeft = 20;
				Logger.LogInformation("workbook's filename as the directory.");
				Console.CursorLeft = 20;
				Logger.LogInformation("If false all sheets will be combined into a file with the");
				Console.CursorLeft = 20;
				Logger.LogInformation("same filename as the workbook.");
				Logger.LogInformation(string.Empty);

				cursorTop = Console.CursorTop;
				Logger.LogInformation("ext");
				Console.CursorTop = cursorTop;
				Console.CursorLeft = 20;
				Logger.LogInformation("Allows you to specify the extension to use when exporting.");
				Logger.LogInformation(string.Empty);

				cursorTop = Console.CursorTop;
				Logger.LogInformation("h|help");
				Console.CursorTop = cursorTop;
				Console.CursorLeft = 20;
				Logger.LogInformation("Displays this help information.");
				return;
			}

			var filePaths = IOHelper.GetExcelFilePaths();

			string splitSheetsString;
			if(!flags.TryGetValue("split-sheets", out splitSheetsString))
				splitSheetsString = "false";
			bool splitSheets;
			if(!bool.TryParse(splitSheetsString, out splitSheets))
			{
				Logger.LogError("Failed to parse the value of split-sheets ({0}) into a boolean; must be true or false.", splitSheetsString);
				splitSheets = false;
			}

			string outputExtension;
			if(!flags.TryGetValue("extension", out outputExtension))
				outputExtension = "txt";
			if(outputExtension.StartsWith("."))
				outputExtension = outputExtension.Substring(1);

			foreach(var filePath in filePaths)
			{
				if(splitSheets)
					ProcessWorkbookWithSplitSheets(filePath, outputExtension);
				else
					ProcessWorkbookWithoutSplitSheets(filePath, outputExtension);
			}
			Console.WriteLine("Press any key to exit...");
			Console.ReadKey(true);
		}

		private static void ProcessWorkbookWithSplitSheets(string filePath, string outputExtension)
		{
			Logger.LogInformation("Starting conversion of:");
			Logger.LogInformation(filePath);
			var cursorTop = Console.CursorTop;

			Application excelApp = null;
			Workbook workbook = null;
			try
			{
				var data = new List<List<string>>();
				excelApp = new Application();
				workbook = excelApp.Workbooks.Open(filePath);

				var directoryPath = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),
					System.IO.Path.GetFileNameWithoutExtension(filePath));

				if(!Directory.Exists(directoryPath))
					Directory.CreateDirectory(directoryPath);

				for(var worksheetIndex = 1; worksheetIndex <= workbook.Worksheets.Count; ++worksheetIndex)
				{
					data.Clear();
					var worksheet = workbook.Sheets[worksheetIndex];

					var outputFilePath = System.IO.Path.Combine(directoryPath,
						string.Format("{1}.{2}", System.IO.Path.GetFileNameWithoutExtension(filePath), worksheet.Name, outputExtension));

					if(System.IO.File.Exists(outputFilePath))
						IOHelper.BackupFile(outputFilePath);

					var totalSize = ProcessWorksheet(worksheet, data);
					if(totalSize == 0)
						continue;

					WriteData(outputFilePath, data, totalSize);
				}
			}
			catch(Exception e)
			{
				Logger.LogInformation("Failed to convert file:");
				Logger.LogError(e.Message);
			}
			finally
			{
				if(workbook != null)
					workbook.Close();

				if(excelApp != null)
					excelApp.Quit();
			}
		}

		private static void ProcessWorkbookWithoutSplitSheets(string filePath, string outputExtension)
		{
			Logger.LogInformation("Starting conversion of:");
			Logger.LogInformation(filePath);

			Application excelApp = null;
			Workbook workbook = null;
			try
			{
				var data = new List<List<string>>();
				excelApp = new Application();
				workbook = excelApp.Workbooks.Open(filePath);

				var outputFilePath = System.IO.Path.Combine(
					System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),
					string.Format("{0}.{1}", System.IO.Path.GetFileNameWithoutExtension(filePath), outputExtension));

				if(System.IO.File.Exists(outputFilePath))
					IOHelper.BackupFile(outputFilePath);

				var totalSize = 0;
				for(var worksheetIndex = 1; worksheetIndex <= workbook.Worksheets.Count; ++worksheetIndex)
				{
					var worksheet = workbook.Sheets[worksheetIndex];
					totalSize += ProcessWorksheet(worksheet, data);
				}

				WriteData(outputFilePath, data, totalSize);
			}
			catch(Exception e)
			{
				Logger.LogInformation("Failed to convert file:");
				Logger.LogError(e.Message);
			}
			finally
			{
				if(workbook != null)
					workbook.Close();

				if(excelApp != null)
					excelApp.Quit();
			}
		}

		private static int ProcessWorksheet(Worksheet worksheet, List<List<string>> rangeData)
		{
			Logger.LogInformation("  Processing worksheet \"{0}\"", worksheet.Name);

			var range = worksheet.UsedRange;
			if(Helper.IsWorksheetRangeEmpty(range))
			{
				Logger.LogWarning("Skipping worksheet because it is empty.");
				return 0;
			}

			var cursorTop = Console.CursorTop;
			var columnCount = range.Columns.Count;
			var rowCount = range.Rows.Count;
			var progressIndex = 0;
			for(var row = 1; row <= rowCount; ++row)
			{
				var rowData = new List<string>(columnCount);
				for(var column = 1; column <= columnCount; ++column)
				{
					if(range[row, column].Value2 == null)
						rowData.Add(string.Empty);
					else
						rowData.Add(range[row, column].Text);

					++progressIndex;

					Console.CursorTop = cursorTop;
					Console.CursorLeft = 0;
					Console.WriteLine(string.Format("    Importing line {0} of {1} - {2}%", row + 1, rowCount + 1, Helper.CalculateProgress(progressIndex, columnCount, rowCount)));
				}
				rangeData.Add(rowData);
			}
			return columnCount * rowCount;
		}

		private static void WriteData(string outputFilePath, List<List<string>> data, int totalSize)
		{
			Logger.LogInformation("  Exporting data to {0}", Path.GetFileName(outputFilePath));
			using(var fileStream = new FileStream(outputFilePath, FileMode.Create))
			{
				var cursorTop = Console.CursorTop;
				var writer = new StreamWriter(fileStream);
				var rowNumber = 1;
				var progressIndex = 0;
				foreach(var rowData in data)
				{
					for(var index = 0; index < rowData.Count; ++index)
					{
						rowData[index] = rowData[index].Trim();
						if(rowData[index].StartsWith("\"") && rowData[index].EndsWith("\""))
							continue;

						rowData[index] = string.Format("\"{0}\"", rowData[index]);
					}

					writer.WriteLine(string.Join(",", rowData));
					progressIndex += rowData.Count;
					++rowNumber;

					Console.CursorTop = cursorTop;
					Console.CursorLeft = 0;
					Console.WriteLine(string.Format("    Exporting record {0} of {1} - {2}%", rowNumber, data.Count + 1, Helper.CalculateProgress(progressIndex, totalSize)));
				}
				writer.Flush();
			}
		}
	}
}
